﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace SwenAssigment.Classes
{
    public class JcUser
    {
        private static SqlConnection scon()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            return conn;
        }
        public static int UpdateUser(int id, int choice, string role, String password, int lockrate)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("UpdateUserProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", id));
            parameters.Add(new SqlParameter("@choice", choice));
            parameters.Add(new SqlParameter("@role", role));
            parameters.Add(new SqlParameter("@password", password));
            parameters.Add(new SqlParameter("@lock", lockrate));
            cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteNonQuery();
        }
        public static int DeleteUser(int id)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("DeleteUserProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", id));
            cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteNonQuery();
        }
        public static int Logged(String username)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("LoggingProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@username", username));
            cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteNonQuery();
        }
        public static int CreateUserAccount(int StaffId, String UserName, String Password, String Role)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("InsertUserProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", StaffId));
            parameters.Add(new SqlParameter("@username", UserName));
            parameters.Add(new SqlParameter("@password", Password));
            parameters.Add(new SqlParameter("@role", Role));
            cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteNonQuery();
        }
        public static ArrayList GetStaffAsUser(int i, String Udata)
        {
            ArrayList ListOfRequest = new ArrayList();
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            if (i == 0)
            {
                comm.CommandText = "SELECT * from [StaffUserCreateView]";
            }
            else if (i == 1)
            {
                comm.CommandText = "SELECT * from [StaffUserCreateView] where Staff_ID = " + Convert.ToInt32(Udata);
            }
            else if (i == 2)
            {
                comm.CommandText = "SELECT * from [StaffUserCreateView] where Staff_Name like '%" + Udata + "%'";
            }
            else
            {
                comm.CommandText = "SELECT * from [StaffUserCreateView] where NRIC = '" + Udata + "'";
            }

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                //Object[] values = new Object[dr.FieldCount];
                //int fieldCount = dr.GetValues(values);
                Staff data = new Staff();
                data.StaffId = (int)dr["Staff_ID"];
                data.StaffName = (String)dr["Staff_Name"];
                data.ContactNumber = (int)dr["Contact_Number"];
                data.Job = (String)dr["Job"];
                data.NRIC = (String)dr["NRIC"];
                ListOfRequest.Add(data);

            }
            return ListOfRequest;
        }
        public static ArrayList GetAllUser(int i, String Udata)
        {
            ArrayList ListOfRequest = new ArrayList();
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            if (i == 0)
            {
                comm.CommandText = "SELECT * from [UserView]";
            }
            else if (i == 1)
            {
                comm.CommandText = "SELECT * from [UserView] where User_Id = " + Convert.ToInt32(Udata);
            }
            else if (i == 2)
            {
                comm.CommandText = "SELECT * from [UserView] where Staff_Name like '%" + Udata + "%'";
            }
            else if (i == 3)
            {
                comm.CommandText = "SELECT * from [UserView] where sUserName like '%" + Udata + "%'";
            }
            else
            {
                comm.CommandText = "SELECT * from [UserView] where Role = '" + Udata + "'";
            }
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                //Object[] values = new Object[dr.FieldCount];
                //int fieldCount = dr.GetValues(values);
                UserDetail data = new UserDetail();
                data.User_ID = (int)dr["User_ID"];
                data.Staff_ID = (int)dr["Staff_ID"];
                data.Username = (String)dr["sUserName"];
                data.Role = (String)dr["Role"];
                data.LockRate = (int)dr["Fail"];
                if (data.LockRate > 0)
                {
                    data.LockStatus = true;
                }
                else
                {
                    data.LockStatus = false;
                }
                data.Staff_Name = (String)dr["Staff_Name"];
                data.Contact_Number = (int)dr["Contact_Number"];
                data.Job = (String)dr["Job"];
                data.NRIC = (String)dr["NRIC"];
                ListOfRequest.Add(data);

            }
            return ListOfRequest;
        }
        public static String UpdatePassword(String Username, String OldPassword, String NewPassword)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("ChangePasswordProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@Username", Username));
            parameters.Add(new SqlParameter("@OldPassword", OldPassword));
            parameters.Add(new SqlParameter("@NewPassword", NewPassword));
            cmd.Parameters.AddRange(parameters.ToArray());
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read() == true)
            {
                return (String)rdr[0];
            }
            return "Fail";
        }
        public static void UpdateRequest(int id, String status)
        {
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand cmd = new SqlCommand("RequestProcedure", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", id));
            parameters.Add(new SqlParameter("@choice", 2));
            parameters.Add(new SqlParameter("@update", status));
            cmd.Parameters.AddRange(parameters.ToArray());
            cmd.ExecuteNonQuery();
        }
        public static void DeletedRequest(int id)
        {
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand cmd = new SqlCommand("RequestProcedure", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", id));
            parameters.Add(new SqlParameter("@choice", 1));
            parameters.Add(new SqlParameter("@update", ""));
            cmd.Parameters.AddRange(parameters.ToArray());
            cmd.ExecuteNonQuery();
        }
        public static ArrayList GetAllRequest()
        {
            ArrayList ListOfRequest = new ArrayList();
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * from [RequestView]";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                //Object[] values = new Object[dr.FieldCount];
                //int fieldCount = dr.GetValues(values);
                RequestAcc data = new RequestAcc();
                data.Request_Id = (int)dr["Request_Id"];
                data.Requester_Id = (int)dr["Requester_Id"];
                data.SUName = (String)dr["SUName"];
                data.rRole = (String)dr["rRole"];
                data.Status = (String)dr["Status"];
                data.Staff_Name = (String)dr["Staff_Name"];
                data.Contact_Number = (int)dr["Contact_Number"];
                data.Job = (String)dr["Job"];
                data.NRIC = (String)dr["NRIC"];
                ListOfRequest.Add(data);

            }
            return ListOfRequest;
        }
        public static ArrayList GetAllRequest(int i, String Udata)
        {
            ArrayList ListOfRequest = new ArrayList();
            SqlConnection conn = scon();
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            if (i == -1)
            {
                comm.CommandText = "SELECT * from [RequestView]";
            }
            else if (i == 0)
            {
                comm.CommandText = "SELECT * from [RequestView] where Staff_Name like '%" + Udata + "%'";
            }
            else if (i == 1)
            {
                comm.CommandText = "SELECT * from [RequestView] where Request_Id = " + Convert.ToInt32(Udata);
            }
            else
            {
                comm.CommandText = "SELECT * from [RequestView] where Status = '" + Udata + "'";
            }
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                //Object[] values = new Object[dr.FieldCount];
                //int fieldCount = dr.GetValues(values);
                RequestAcc data = new RequestAcc();
                data.Request_Id = (int)dr["Request_Id"];
                data.Requester_Id = (int)dr["Requester_Id"];
                data.SUName = (String)dr["SUName"];
                data.rRole = (String)dr["rRole"];
                data.Status = (String)dr["Status"];
                data.Staff_Name = (String)dr["Staff_Name"];
                data.Contact_Number = (int)dr["Contact_Number"];
                data.Job = (String)dr["Job"];
                data.NRIC = (String)dr["NRIC"];
                ListOfRequest.Add(data);

            }
            return ListOfRequest;
        }

        public static String CreateAccount(int rId, String rName, String BankAcc, String rNRIC, String UName, String UPassword, String Role)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("CreateUserProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@sId", rId));
            parameters.Add(new SqlParameter("@sName", rName));
            parameters.Add(new SqlParameter("@sAcc", BankAcc));
            parameters.Add(new SqlParameter("@sNRIC", rNRIC));
            parameters.Add(new SqlParameter("@cUPassword", UPassword));
            parameters.Add(new SqlParameter("@cUName", UName));
            parameters.Add(new SqlParameter("@rRole", Role));
            cmd.Parameters.AddRange(parameters.ToArray());
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read() == true)
            {
                if ((String)rdr[0] == "Staff Information Error")
                {
                    return "Staff Information Error";
                }
                else if ((String)rdr[0] == "User Name Exist")
                {
                    return "User Name Exist";
                }
                else if ((String)rdr[0] == "Have request before")
                {
                    return "Have request before";
                }
                else
                {
                    return (String)rdr[0];
                }
            }
            else
            {

            }
            return "";
        }
        public static void LockSetting(int id, int value)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("LockProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@id", id));
            parameters.Add(new SqlParameter("@value", value));
            cmd.Parameters.AddRange(parameters.ToArray());
            cmd.ExecuteNonQuery();
        }
        public static String Login(String uName, String uWord)
        {
            SqlConnection conn = scon();
            SqlCommand cmd = new SqlCommand("LoginProcedure", conn);
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@username", uName));
            parameters.Add(new SqlParameter("@password", uWord));
            cmd.Parameters.AddRange(parameters.ToArray());
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read() == true)
            {
                try
                {
                    if ((Int32)rdr[0] >= 3)
                    {
                        return "lock";
                    }
                }
                catch (Exception ex)
                {
                    return (String)rdr[1];
                }
            }
            else
            {
                //
            }
            return "fail";
        }
    }
}